<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class M_subjects extends MY_Model{
	function add_subject($subject_data){
		$this->db->insert('subjects', $subject_data);
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}	
	
	function remove_subject($subject_id){
		$this->db->where('subj_id', $subject_id)->delete('subjects');
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	public function fetch_subjects($where = false,$limit,$start)
	{
		if($where == false):
			if($this->c_e_gl != '' && !empty($this->c_e_gl)):
				$levels = explode( ',', $this->c_e_gl );
				$q = $this->db->select('s.*,l.level_desc')
							  ->from('subjects s')
							  ->join('levels l','l.level_id = s.grade_level_id','left')
							  ->where_in('s.grade_level_id',$levels)
							  ->limit($limit,$start)
							  ->get();
				return $q->num_rows() > 0 ? $q->result() : FALSE;
			else:
				$q = $this->db->select('s.*,l.level_desc')
							  ->from('subjects s')
							  ->join('levels l','l.level_id = s.grade_level_id','left')
							  ->limit($limit,$start)
							  ->get();
				return $q->num_rows() > 0 ? $q->result() : FALSE;
			endif;
		else:
			if($this->c_e_gl != '' && !empty($this->c_e_gl)):
				$levels = explode( ',', $this->c_e_gl );
				$q = $this->db->select('s.*,l.level_desc')
							  ->or_like($where,'BOTH')
							  ->from('subjects s')
							  ->join('levels l','l.level_id = s.grade_level_id','left')
							  ->where_in('s.grade_level_id',$levels)
							  ->limit($limit,$start)
							  ->get();	
				return $q->num_rows() > 0 ? $q->result() : FALSE;
			else:
				$q = $this->db->select('s.*,l.level_desc')
							  ->or_like($where,'BOTH')
							  ->from('subjects s')
							  ->join('levels l','l.level_id = s.grade_level_id','left')
							  ->limit($limit,$start)
							  ->get();	
				return $q->num_rows() > 0 ? $q->result() : FALSE;
			endif;
		endif;
	}
	
	public function count_fetch_subjects($where)
	{
		if($this->c_e_gl != '' && !empty($this->c_e_gl)):
			$levels = explode( ',', $this->c_e_gl );
			$q = $this->db->select('count(*) as totals')
						  ->where_in('grade_level_id',$levels)
						  ->or_like($where,'BOTH')
						  ->from('subjects')
						  ->get();		
			return $q->num_rows() > 0 ? $q->row()->totals : FALSE;
		else:
			$q = $this->db->select('count(*) as totals')
						  ->or_like($where,'BOTH')
						  ->from('subjects')
						  ->get();		
			return $q->num_rows() > 0 ? $q->row()->totals : FALSE;
		endif;
	}
	
	function count_all_subjects(){
		if($this->c_e_gl != '' && !empty($this->c_e_gl)):
			$sql = "SELECT subj_id
					FROM subjects  
					WHERE grade_level_id IN ({$this->c_e_gl}) ";
			$query = $this->db->query($sql);
			return $query->num_rows() > 0 ? $query->result() : FALSE;
		endif;
	}
	
	function get_all_subjects(){
			$q = $this->db->select('s.*,l.level_desc')->order_by('subj_stat', 'asc')->join('levels l','l.level_id = s.grade_level_id','left')->get('subjects s');
			return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	function get_subject($subject_id){
		$q = $this->db->where('subj_id', $subject_id)->get('subjects');
		return $q->num_rows() >= 1 ? $q->row() : FALSE;
	}
	
	function edit_subject($subject_data, $subject_id){
		$subject_data['for_academic_year'] = $this->c_sy->id;
		$this->db->where('subj_id', $subject_id)->update('subjects', $subject_data);
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	function activate($subject_id, $updated){
		$this->db->where('subj_id', $subject_id)->update('subjects', array('subj_stat' => 'active', 'subj_updated' => $updated));
	}
	
	function deactivate($subject_id, $updated){
		$this->db->where('subj_id', $subject_id)->update('subjects', array('subj_stat' => 'inactive', 'subj_updated' => $updated));
	}
	
	function get_all_section_subjects(){
		$q = $this->db->query('SELECT sections.id AS section_id, section_name, section_total.total FROM sections LEFT JOIN (SELECT count(section_id) as total, section_id FROM section_subjects GROUP BY section_id) AS section_total ON section_total.section_id = sections.id');
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	function get_all_assigned_subjects($section_id){
		$q = $this->db->query("SELECT subjects.subj_id, subjects.subj_code, subjects.subj_desc  FROM section_subjects LEFT JOIN subjects ON section_subjects.subj_id = subjects.subj_id WHERE section_subjects.section_id = $section_id");
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	function get_other_subjects($section_id){
		$q = $this->db->query("SELECT * FROM subjects WHERE subjects.subj_id NOT IN (SELECT subj_id FROM section_subjects WHERE section_id = '$section_id')");
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	function assign_subjects($subjects){
		$this->db->insert_batch('section_subjects',$subjects);
		
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	function unassign_subjects($ss_id, $section_id){
		$this->db->where('section_id', $section_id)->where_in('subj_id', $ss_id)->delete('section_subjects');
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	function get_section_subject_ids($section_id){
		$q = $this->db->select('subj_id')->where('section_id', $section_id)->get('section_subjects');
		return $q->num_rows >= 1 ? $q->result() : FALSE;
	}
	public function get_specific_coloumn($data)
	{	
		$query = $this->db->select($data)->get('subjects');
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}	
	
	public function get_subjects_by_grade_level($grade_level_id)
	{
		$sql = "Select * FROM subjects WHERE subjects.grade_level_id = ? ";
		
		$q = $this->db->query($sql, $grade_level_id);
		return $q->num_rows() > 0 ? $q->result() : FALSE;
	}
	
	public function get_assigned_subjects_to_teacher($id)
	{
		$query = "SELECT * FROM schedule 
					LEFT JOIN subjects ON subject_id = subj_id
					WHERE teacher_id = ?";
		$q = $this->db->query($query, array($id));
		return $q->num_rows() > 0 ? $q->result() : FALSE;
	}
}

?>